CREATE TABLE [dbo].[CampaignMain]
(
[CampaignKey] [uniqueidentifier] NOT NULL,
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LegacyCampaignCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CampaignTypeCode] [int] NOT NULL CONSTRAINT [DF_CampaignMain_CampaignTypeCode] DEFAULT ((2)),
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_CampaignMain_Description] DEFAULT (''),
[CampaignStatusCode] [int] NOT NULL CONSTRAINT [DF_CampaignMain_CampaignStatusCode] DEFAULT ((0)),
[CostCollection] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_TargetRevenue] DEFAULT ((0)),
[PredictedResponseRate] [decimal] (5, 4) NOT NULL CONSTRAINT [DF_CampaignMain_PredictedResponseRate] DEFAULT ((0)),
[TotalRevenue] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_TotalRevenue] DEFAULT ((0)),
[PrimaryOwnerUserKey] [uniqueidentifier] NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_CampaignMain_UpdatedOn] DEFAULT (getdate()),
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[LowResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_LowResponseAmount] DEFAULT ((0)),
[HighResponseAmount] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_HighResponseAmount] DEFAULT ((0)),
[ActualCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_ActualCost] DEFAULT ((0)),
[EstimatedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_EstimatedCost] DEFAULT ((0)),
[ExtendedCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_ExtendedCost] DEFAULT ((0)),
[OverheadCost] [decimal] (18, 4) NOT NULL CONSTRAINT [DF_CampaignMain_OverheadCost] DEFAULT ((0)),
[FirstResponseDate] [datetime] NULL,
[LastResponseDate] [datetime] NULL,
[TotalPositiveResponse] [int] NOT NULL CONSTRAINT [DF_CampaignMain_TotalPositiveResponse] DEFAULT ((0)),
[TotalNegativeResponse] [int] NOT NULL CONSTRAINT [DF_CampaignMain_TotalNegativeResponse] DEFAULT ((0)),
[TotalSolicited] [int] NOT NULL CONSTRAINT [DF_CampaignMain_TotalSolicited] DEFAULT ((0)),
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[MarkedForDeleteOn] [datetime] NULL,
[AccessKey] [uniqueidentifier] NOT NULL,
[SystemEntityKey] [uniqueidentifier] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_CampaignMain_Delete]
    ON  [dbo].[CampaignMain]
    FOR DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM Campaign WHERE CAMPAIGN_CODE IN (SELECT LegacyCampaignCode FROM deleted)
END

GO
CREATE TRIGGER [dbo].[asi_CampaignMain_Insert]
    ON  [dbo].[CampaignMain]
    FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Campaign (CAMPAIGN_CODE, TITLE, DESCRIPTION, DEFAULT_FUND,
                          GOAL, BEGINS_DATE, ENDS_DATE, IS_PERPETUAL)
    SELECT [LegacyCampaignCode], [Name], IsNULL([Description],''), '',
           TargetRevenue, StartDate, EndDate, 0
    FROM inserted
END

GO
CREATE TRIGGER [dbo].[asi_CampaignMain_Update]
    ON  [dbo].[CampaignMain]
    FOR UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE Campaign
    SET
        TITLE = i.[Name],
        DESCRIPTION = IsNull(i.Description,''),
        GOAL = i.TargetRevenue,
        BEGINS_DATE = i.StartDate,
        ENDS_DATE = i.EndDate,
        CAMPAIGN_CODE = i.LegacyCampaignCode
    FROM inserted i INNER JOIN deleted d ON i.CampaignKey = d.CampaignKey
    WHERE CAMPAIGN_CODE = d.LegacyCampaignCode

    UPDATE Appeal
    SET CAMPAIGN_CODE = i.LegacyCampaignCode
    FROM inserted i INNER JOIN deleted d ON i.CampaignKey = d.CampaignKey
    WHERE CAMPAIGN_CODE = d.LegacyCampaignCode

END

GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [PK_CampaignMain] PRIMARY KEY CLUSTERED ([CampaignKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_AccessKey] ON [dbo].[CampaignMain] ([AccessKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_CampaignStatusCode] ON [dbo].[CampaignMain] ([CampaignStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_CampaignTypeCode] ON [dbo].[CampaignMain] ([CampaignTypeCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_CreatedByUserKey] ON [dbo].[CampaignMain] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_PrimaryOwnerUserKey] ON [dbo].[CampaignMain] ([PrimaryOwnerUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_SystemEntityKey] ON [dbo].[CampaignMain] ([SystemEntityKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CampaignMain_UpdatedByUserKey] ON [dbo].[CampaignMain] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_AccessMain] FOREIGN KEY ([AccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_CampaignStatusRef] FOREIGN KEY ([CampaignStatusCode]) REFERENCES [dbo].[CampaignStatusRef] ([CampaignStatusCode])
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_CampaignTypeRef] FOREIGN KEY ([CampaignTypeCode]) REFERENCES [dbo].[CampaignTypeRef] ([CampaignTypeCode])
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_SystemEntity] FOREIGN KEY ([SystemEntityKey]) REFERENCES [dbo].[SystemEntity] ([SystemEntityKey])
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_UniformRegistry] FOREIGN KEY ([CampaignKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[CampaignMain] ADD CONSTRAINT [FK_CampaignMain_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO